﻿Imports System.Security

Public Class RglementNouv

#Region "Declaration"
    Dim db As New Connexion
    Dim Entrer, action As String
    Dim OpenFileDialog1 As New OpenFileDialog
    Dim strFichierSource As String
    Dim strFichierCible As String = ""
    Public databaseName As String
#End Region

    Private Sub RglementNouv_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Me.Dispose()
    End Sub

    Private Sub RglementNouv_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Entrer = ""
        Me.ComboBoxListRevUpdate.Visible = False
        Me.ComboBoxTypCode.Visible = False
        Me.ComboBoxCatgCode.Visible = False
        DataGridViewTypeHab.MultiSelect = False
        DataGridViewTypeHab.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGridViewTypeHab.ReadOnly = True

        majCatg()
        majTyp()
        majRegl()
    End Sub
    'Methode de mise a jour de la liste des categories du reglement
    Private Sub majCatg()
        Try
            Entrer = ""
            db.myReq = "Select * FROM  [dbo].[ReglCateg2]"
            db.Connecter(databaseName)
            Me.ComboBoxCatgCode.Items.Clear()
            Me.ComboBoxCatgNom.Items.Clear()
            Me.ComboBoxCatgCode.Items.Add("")
            Me.ComboBoxCatgNom.Items.Add("Tous")

            While db.rdr.Read()
                Me.ComboBoxCatgCode.Items.Add(db.rdr(0))
                Me.ComboBoxCatgNom.Items.Add(db.rdr(2))
            End While
            Me.ComboBoxCatgCode.SelectedIndex = 0
            Me.ComboBoxCatgNom.SelectedIndex = 0
            db.Disconnecter()
        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try
    End Sub
    'Methode de mise a jour de la liste des types du reglement selon la categorie choisie
    Private Sub majTyp()
        Try
            Entrer = ""
            If Me.ComboBoxCatgNom.Text = "Tous" Then
                db.myReq = "Select * FROM  [dbo].[RglttnTyp2]"
            Else
                db.myReq = "Select * FROM  [dbo].[RglttnTyp2] where [Id_ReglCateg] ='" & Me.ComboBoxCatgCode.Text & "'"
            End If

            db.Connecter(databaseName)
            Me.ComboBoxTypCode.Items.Clear()
            Me.ComboBoxTypNom.Items.Clear()
            Me.ComboBoxTypCode.Items.Add("")
            Me.ComboBoxTypNom.Items.Add("Tous")

            While db.rdr.Read()
                Me.ComboBoxTypCode.Items.Add(db.rdr(0))
                Me.ComboBoxTypNom.Items.Add(db.rdr(2))
            End While
            Me.ComboBoxTypCode.SelectedIndex = 0
            Me.ComboBoxTypNom.SelectedIndex = 0
            db.Disconnecter()

        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try
    End Sub

    Private Sub majRegl()
        Try
            ComboBoxListRevUpdate.Items.Clear()
            Dim idReg As String = ""

            If Me.ComboBoxTypCode.Text = "" Then
                db.myReq = "SELECT * FROM  [dbo].[Rglttn]"
            Else
                db.myReq = "SELECT * FROM  [dbo].[Rglttn] where [ID_RegTyp]=" & Me.ComboBoxTypCode.Text
            End If

            db.Connecter(databaseName)
            DataGridViewTypeHab.Rows.Clear()
            While db.rdr.Read()
                DataGridViewTypeHab.Rows.Add(db.rdr(0), db.rdr(1), db.rdr(2), db.rdr(3), db.rdr(4), db.rdr(5), db.rdr(6), db.rdr(7))
                idReg = db.rdr(0)
            End While
            db.Disconnecter()

            db.myReq = "SELECT * FROM  [dbo].[RevsionReg] where [Id_rgl]='" & idReg & "'"
            db.Connecter(databaseName)
            ComboBoxListRev.Items.Clear()
            While db.rdr.Read()
                ComboBoxListRev.Items.Add(db.rdr(2))
            End While
            db.Disconnecter()

            If ComboBoxListRev.Items.Count > 0 Then
                Me.ComboBoxListRev.SelectedIndex = 0
            End If


            If DataGridViewTypeHab.RowCount - 1 = 0 Then
                Me.TextBoxIntitule.Text = ""
                Me.TextBoxCode.Text = ""
                Me.TextBoxCmt.Text = ""

            Else
                DataGridViewTypeHab.Rows(0).Selected = True
                Me.TextBoxIntitule.Text = DataGridViewTypeHab.SelectedCells(3).Value
                Me.TextBoxCode.Text = DataGridViewTypeHab.SelectedCells(2).Value
                Me.TextBoxCmt.Text = DataGridViewTypeHab.SelectedCells(6).Value
                Me.DateTimePickerEmis.Text = DataGridViewTypeHab.SelectedCells(4).Value
                Me.DateTimePickerAppl.Text = DataGridViewTypeHab.SelectedCells(5).Value


            End If

        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try

    End Sub


    Private Sub ComboBoxCatgNom_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBoxCatgNom.TextChanged
        Try
            If Me.ComboBoxCatgNom.FindString(Me.ComboBoxCatgNom.Text) > 0 Then
                Dim Pos As Int32 = Me.ComboBoxCatgNom.Text.Length
                Me.ComboBoxCatgNom.SelectedIndex = Me.ComboBoxCatgNom.FindString(Me.ComboBoxCatgNom.Text)
                Me.ComboBoxCatgNom.SelectionStart = Pos
                Me.ComboBoxCatgNom.SelectionLength = Me.ComboBoxCatgNom.Text.Length - Pos
                Me.ComboBoxCatgCode.SelectedIndex = Me.ComboBoxCatgNom.FindString(Me.ComboBoxCatgNom.Text)
            Else
                Me.ComboBoxCatgCode.Text = ""
            End If
        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try
        majTyp()
    End Sub


    Private Sub ComboBoxTypNom_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBoxTypNom.TextChanged
        Try
            If Me.ComboBoxTypNom.FindString(Me.ComboBoxTypNom.Text) > 0 Then
                Dim Pos As Int32 = Me.ComboBoxTypNom.Text.Length
                Me.ComboBoxTypNom.SelectedIndex = Me.ComboBoxTypNom.FindString(Me.ComboBoxTypNom.Text)
                Me.ComboBoxTypNom.SelectionStart = Pos
                Me.ComboBoxTypNom.SelectionLength = Me.ComboBoxTypNom.Text.Length - Pos
                Me.ComboBoxTypCode.SelectedIndex = Me.ComboBoxTypNom.FindString(Me.ComboBoxTypNom.Text)
            Else
                Me.ComboBoxTypCode.Text = ""
            End If
        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try
        majRegl()
    End Sub

    Private Sub DataGridViewTypeHab_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridViewTypeHab.CurrentCellChanged
        If Entrer = "debut" Then
            selection()
        End If
    End Sub
    Private Sub DataGridViewTypeHab_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridViewTypeHab.MouseClick
        If e.Button = MouseButtons.Left Then
            Entrer = "debut"
            selection()
        End If
    End Sub


    Private Sub selection()
        Try
            If Not DataGridViewTypeHab.SelectedCells(2).Value = "" Then
                Me.TextBoxIntitule.Text = DataGridViewTypeHab.SelectedCells(3).Value
                Me.TextBoxCode.Text = DataGridViewTypeHab.SelectedCells(2).Value
                Me.TextBoxCmt.Text = DataGridViewTypeHab.SelectedCells(6).Value
                Me.DateTimePickerEmis.Text = DataGridViewTypeHab.SelectedCells(4).Value
                Me.DateTimePickerAppl.Text = DataGridViewTypeHab.SelectedCells(5).Value

                db.myReq = "SELECT * FROM  [dbo].[RevsionReg] where [Id_rgl]='" & DataGridViewTypeHab.SelectedCells(0).Value & "'"
                db.Connecter(databaseName)
                ComboBoxListRev.Items.Clear()
                ComboBoxListRevUpdate.Items.Clear()
                While db.rdr.Read()
                    ComboBoxListRev.Items.Add(db.rdr(2))
                End While
                db.Disconnecter()

                If ComboBoxListRev.Items.Count > 0 Then
                    Me.ComboBoxListRev.SelectedIndex = 0
                    Me.DateTimePickerRev.Text = Me.ComboBoxListRev.Items.Item(0)
                End If
            End If
        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try
    End Sub

    Private Sub ButtonNouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonNouv.Click
        Me.TextBoxIntitule.Text = ""
        Me.TextBoxCode.Text = ""
        Me.TextBoxCmt.Text = ""
        ComboBoxListRev.Items.Clear()
        ComboBoxListRevUpdate.Items.Clear()
        action = "ajouter"
    End Sub

    Private Sub ButtonEnregistrer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonEnregistrer.Click
        Try
            Entrer = ""
            If action = "ajouter" Then
                db.myReq = "INSERT INTO  [dbo].[Rglttn]([ID_RegTyp],[COD_RGL],[NOM_RGL],[DATDEC_RGL],[DATAPP_RGL],[NOTES_RGL],[Chem_rgl])VALUES('" & Me.ComboBoxTypCode.Text & "','" & Me.TextBoxCode.Text & "','" & Me.TextBoxIntitule.Text & "','" & Me.DateTimePickerEmis.Text & "','" & Me.DateTimePickerAppl.Text & "','" & Me.TextBoxCmt.Text & "','" & strFichierCible & "')"
                db.Connecter(databaseName)
                db.Disconnecter()

                Dim idReg As Integer = 0
                db.myReq = "SELECT Max([Id_rgl]) FROM  [dbo].[RGLTTN]"
                db.Connecter(databaseName)
                While db.rdr.Read
                    idReg = db.rdr(0)
                End While
                db.Disconnecter()

                Dim taille As Integer = Me.ComboBoxListRev.Items.Count
                Dim inter As Integer = 0
                While inter < taille
                    db.myReq = "INSERT INTO  [dbo].[RevsionReg]([Id_rgl],[Date_Rev])VALUES('" & idReg & "','" & Me.ComboBoxListRev.Items.Item(inter) & "')"
                    db.Connecter(databaseName)
                    db.Disconnecter()
                    inter = inter + 1
                End While

            Else
                db.myReq = "Update  [dbo].[Rglttn] SET  [ID_RegTyp] = '" & Me.DataGridViewTypeHab.SelectedCells(1).Value & "', [COD_RGL] = '" & Me.TextBoxCode.Text & "',[NOM_RGL] = '" & Me.TextBoxIntitule.Text & "',[DATDEC_RGL]= '" & Me.DateTimePickerEmis.Text & "', [DATAPP_RGL]= '" & Me.DateTimePickerAppl.Text & "',[NOTES_RGL]= '" & Me.TextBoxCmt.Text & "',[Chem_rgl]='" & strFichierCible & "'  WHERE [Id_rgl]='" & DataGridViewTypeHab.SelectedCells(0).Value & "'"
                Dim taille As Integer = Me.ComboBoxListRevUpdate.Items.Count

                Dim inter As Integer = 0
                While inter < taille
                    db.myReq = "INSERT INTO  [dbo].[RevsionReg]([Id_rgl],[Date_Rev])VALUES('" & Me.DataGridViewTypeHab.SelectedCells(1).Value & "','" & Me.ComboBoxListRevUpdate.Items.Item(inter + 1) & "')"
                    MsgBox(db.myReq)
                    db.Connecter(databaseName)
                    db.Disconnecter()
                    inter = inter + 1
                End While
            End If

        Catch ex As Exception
            MsgBox("Problème de connexion")
        End Try

        majRegl()

    End Sub

    Private Sub ButtonDoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonDoc.Click

        Dim dr As DialogResult = Me.OpenFileDialog1.ShowDialog()
        If Not dr = System.Windows.Forms.DialogResult.Cancel Then
            ' Read the files
            Dim file As String
            For Each file In OpenFileDialog1.FileNames

                Try

                    strFichierSource = file
                    ' A remplacer
                    strFichierCible = AddBackslash("C:\Users\Altaf\Documents\Visual Studio 2010\Projects\SILM_V_06_2012\SILM_V_06_2012\ImporterDocument") & Filename(file)

                    ' Si le document originale existe, on le copie...
                    If Dir(strFichierSource) <> "" Then
                        FileCopy(strFichierSource, strFichierCible)
                    End If

                Catch SecEx As SecurityException
                    MessageBox.Show("Erreur de sécutité. Veillez  contacter votre administrateur pour plus de détails.\n\n" & _
                        "Error message: " & SecEx.Message & "\n\n" & _
                        "Details (send to Support):\n\n" & SecEx.StackTrace)
                Catch ex As Exception
                    ' Could not load the image - probably permissions-related.
                    MessageBox.Show(("L'image ne peut pas être afficher: " & file.Substring(file.LastIndexOf("\"c)) & _
                    ". ou vous n'avez pas la pérmission de la lecture, ou bien " + "" _
                    & ControlChars.Lf & ControlChars.Lf & "Reported error: " & ex.Message))
                End Try
            Next file
        End If
    End Sub

    Function AddBackslash(ByVal strFolder As String) As String
        strFolder = Trim(strFolder)
        If Mid(strFolder, strFolder.Length - 1, 1) <> "\" Then strFolder = strFolder & "\"
        AddBackslash = strFolder
    End Function

    Function Filename(ByVal strPath As String) As String
        ' Trouver le dernier backslash, s'il y en a un...
        Dim intI As Integer
        intI = InStrRev(strPath, "\", -1, vbTextCompare)
        ' Renvoyer la partie après le backslash
        Filename = IIf(intI = 0, strPath, Mid(strPath, intI + 1))
    End Function

    Private Sub ButtonValide_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonValide.Click
        Dim d As Date = Me.DateTimePickerRev.Text
        Me.ComboBoxListRev.Items.Add(d)
        Me.ComboBoxListRevUpdate.Items.Add(d)
    End Sub

    Private Sub ButtonMod_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonMod.Click
        ComboBoxListRevUpdate.Items.Clear()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    End Sub

    Private Sub ButtonNouvTypSite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonNouvTypSite.Click
        Dim typX As New TypologieNivX
        If Not Me.ComboBoxCatgCode.Text = "" Then
            Dim index As Integer = typX.ComboBoxNomTypol.Items.IndexOf(Me.ComboBoxCatgNom.Text)
            typX.indexPublic = index
        End If
        typX.Text = "Nivvv"
        typX.Table1 = "ConcernCateg"
        typX.Table2 = "ConcernTyp"
        typX.Visible = True
      
    End Sub

End Class